﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;

namespace Db
{
    public class MemberDb : AbstractDb
    {

        public Member GetMemberById(int id)
        {
            Member mem = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Person JOIN Bif_Member ON person_ID = member_personID WHERE person_id = " + id);
            if (q.HasRows && q.Read())
            {
                int uid = q.GetInt32(q.GetOrdinal("person_ID"));
                string email = q.GetString(q.GetOrdinal("person_email"));
                string fName = q.GetString(q.GetOrdinal("person_firstname"));
                string lName = q.GetString(q.GetOrdinal("person_lastname"));
                string phone = q.GetString(q.GetOrdinal("person_email"));
                int postalcode = 0;
                //int postalcode = q.GetInt32(q.GetOrdinal("person_postalcode"));
                string city = null;
                //string city = q.GetString(q.GetOrdinal("city_city"));
                int memberNr = q.GetInt32(q.GetOrdinal("member_memberNr"));
                byte newsletter = q.GetByte(q.GetOrdinal("member_newsletter"));
                byte specialOffer = q.GetByte(q.GetOrdinal("member_specialOffer"));
                string password = q.GetString(q.GetOrdinal("person_password"));

                mem = new Member(uid, fName, lName, email, phone, memberNr, postalcode, city, newsletter, specialOffer, password);
            }

            return mem;
        }

        public int CreateMember(Member newMember)
        {
            int result = 0;
            if (newMember != null)
            {
                Console.WriteLine("Navn: " + newMember.FName);
                Statement stmtPerson = new Statement("INSERT INTO Bif_Person (person_email,  person_password, person_firstname, person_lastname, person_phone) VALUES (@Email, @Password, @FName, @LName, @Phone)");

                stmtPerson.AddParameter("@Email", newMember.Email, "string");
                stmtPerson.AddParameter("@Password", newMember.Password, "string");
                stmtPerson.AddParameter("@FName", newMember.FName, "string");
                stmtPerson.AddParameter("@LName", newMember.LName, "string");
                stmtPerson.AddParameter("@Phone", newMember.Phone, "string");

                int idPerson = this.db.QueryInsert(stmtPerson);

                if (idPerson > 0)
                {
                    Statement stmtMember = new Statement("INSERT INTO Bif_Member (member_personID, member_newsletter, member_specialOffer) VALUES (@PersonID, @Newsletter, @SpecialOffer)");
                    stmtMember.AddParameter("@PersonID", idPerson, "string");
                    stmtMember.AddParameter("@Newsletter", newMember.Newsletter, "tinyint");
                    stmtMember.AddParameter("@SpecialOffer", newMember.SpecialOffer, "tinyint");
                    int res = this.db.QueryUpdate(stmtMember);

                    if (res > 0)
                    {
                        result = idPerson;
                    }
                    else
                    {
                        Statement stmtDeletePerson = new Statement("DELETE FROM Bif_Person WHERE person_ID =" + idPerson);
                        int del = this.db.QueryDelete(stmtDeletePerson);
                    }
                }
            }

            return result;

        }

        public Boolean UpdateMember(Member member)
        {
            Transaction tran = new Transaction();
            Statement stmtp = new Statement("UPDATE Bif_Person set person_email = @Email, person_firstname = @FName, person_lastname = @LName, person_phone = @Phone, person_password = @Password WHERE person_ID = @PersonId");
            Statement stmtm = new Statement("UPDATE Bif_Member set member_newsletter = @Newsletter, member_specialOffer = @SpecialOffer WHERE member_personID=@MemberId");
            stmtp.AddParameter("@Email", member.Email, "string");
            stmtp.AddParameter("@FName", member.FName, "string");
            stmtp.AddParameter("@LName", member.LName, "string");
            stmtp.AddParameter("@Phone", member.Phone, "string");
            stmtp.AddParameter("@Password", member.Password, "string");
            stmtp.AddParameter("@Personid", member.Id, "int");

            stmtm.AddParameter("@Newsletter", member.Newsletter, "tinyint");
            stmtm.AddParameter("@SpecialOffer", member.SpecialOffer, "tinyint");
            stmtm.AddParameter("@MemberId", member.Id, "int");
            
            tran.Add(stmtm);
            tran.Add(stmtp);

            return db.CommitTransaction(tran); 
        
        }

        public Member GetMemberByName(string fname, string lname)
        {
            Member mem = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Person JOIN Bif_Member ON person_ID = member_personID WHERE person_firstname = " + fname + ", person_lastname = " + lname);
            if (q.HasRows && q.Read())
            {
                int uid = q.GetInt32(q.GetOrdinal("person_ID"));
                string email = q.GetString(q.GetOrdinal("person_email"));
                string fName = q.GetString(q.GetOrdinal("person_firstname"));
                string lName = q.GetString(q.GetOrdinal("person_lastname"));
                string phone = q.GetString(q.GetOrdinal("person_email"));
                int postalcode = 0;
                //int postalcode = q.GetInt32(q.GetOrdinal("person_postalcode"));
                string city = null;
                //string city = q.GetString(q.GetOrdinal("city_city"));
                int memberNr = q.GetInt32(q.GetOrdinal("member_memberNr"));
                byte newsletter = q.GetByte(q.GetOrdinal("member_newsletter"));
                byte specialOffer = q.GetByte(q.GetOrdinal("member_specialOffer"));
                string password = q.GetString(q.GetOrdinal("person_password"));
                
                mem = new Member(uid, fName, lName, email, phone, memberNr, postalcode, city, newsletter, specialOffer, password);
            }

            return mem;
        }

        public Boolean DeleteMember(int id)
        {
            Transaction tran = new Transaction();
            Statement stmtm = new Statement("DELETE FROM Bif_Member WHERE member_personID=" + id );
            Statement stmtp = new Statement("DELETE FROM Bif_Person WHERE person_ID=" + id);
            tran.Add(stmtm);
            tran.Add(stmtp);
            return this.db.CommitTransaction(tran);
        }

        public List<Member> GetAllMembers()
        {
            List<Member> memberlist = new List<Member>();
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Member LEFT JOIN Bif_Person ON member_personID = person_ID ");
             if(q != null && q.HasRows)
             {
                 while (q.Read())
                 {
                     int id = q.GetInt32(q.GetOrdinal("person_id"));
                     string fName = q.GetString(q.GetOrdinal("person_firstname"));
                     string lName = q.GetString(q.GetOrdinal("person_lastname"));
                     string phone = q.GetString(q.GetOrdinal("person_phone"));
                     string password = q.GetString(q.GetOrdinal("person_password"));
                     string email = q.GetString(q.GetOrdinal("person_email"));
                     int memberNr = q.GetInt32(q.GetOrdinal("member_memberNr"));
                     byte newsletter = q.GetByte(q.GetOrdinal("member_newsletter"));
                     byte specialOffer = q.GetByte(q.GetOrdinal("member_specialOffer"));
                     Member memberObj = new Member(id, fName, lName, email, phone, newsletter, specialOffer, password, memberNr);
                     memberlist.Add(memberObj);
                 }
             }
             return memberlist;
        }


    }
}
